# -*- coding:utf-8 -*-
#!/usr/bin/python3
# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
import pymysql


class DoubantopPipeline:
    def mysql_storage(self, data):   # 结果保存到mysql
        # 需要的数据：id，书名，作者，出版社，出版年，页数，定价，装帧，丛书，ISBN，豆瓣评分，内容简介
        # 主键是书名+id
        # 打开数据库连接
        db = pymysql.connect(host='localhost',
                             user='root',
                             port=3306,  # 端口
                             password='',
                             database='mysql')
        # 使用cursor()方法获取操作游标
        cursor = db.cursor()
        # SQL 语句
        print(f'''进入mysql_storage的data:{data}''')
        sql_insert = f'''
        INSERT INTO DOU_BAN_BOOK_TOP250
        (book_name,author,press,publication_year,page_num,price,binding,series,ibsn,score,content)
        VALUES('{data['book_name']}','{data['author']}','{data['press']}','{data['publication_year']}','{data['page_num']}','{data['price']}','{data['binding']}','{data['series']}','{data['ibsn']}','{data['score']}','{data['content']}');'''
        # 执行SQL语句
        cursor.execute(sql_insert)
        db.commit()   # 提交事务数据
        # 关闭数据库连接
        db.close()

    def process_item(self, item, spider):
        # print(f'''进入process_item的item:{item}''')
        self.mysql_storage(item)
        return item

#
# if __name__ == '__main__':
#     d1 = DoubantopPipeline()
#     data={'id': 1, 'book_name': '红楼梦', 'author': '曹雪芹'
#         , 'press': '人民文学出版社', 'publication_year': '1996-12-01',
#         'page_num': 1606, 'price': 59.70
#         , 'binding': '平装', 'series': '中国古典文学读本丛书',
#         'ibsn': '9787020002207', 'score': 9.6, 'content': '《红楼梦》是一部百科全书式的长篇小说'
#     }
#     d1.mysql_storage(data)
